﻿
-- ================================================
-- Remarks: Not part of standard Stored Proc
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_Sys_RoleActivity_GetAllByRoleKey')
	BEGIN
		DROP  Procedure  Ivy_Sys_RoleActivity_GetAllByRoleKey
	END

GO

CREATE PROCEDURE [Ivy_Sys_RoleActivity_GetAllByRoleKey] 
-- =============================================
-- Author:		<Ram pravesh kr>
-- Create date: <09/06/2016>
-- Description:	<Ivy_Sys_RoleActivity_GetAllByRoleKey>
-- =============================================
-- Add the parameters for the stored procedure here
	(
				@RoleKey int
				
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

	SELECT  [Sys_ActivityMaster].[ActivityKey], 
	        [Sys_ActivityMaster].[ActivityCode], 
			[Sys_ActivityMaster].[ActivityName], 
			[Sys_ActivityMaster].[ActivityDescription], 
			[Sys_ActivityTypeMaster].[ActivityTypeCode], 
            [Sys_ActivityTypeMaster].[ActivityTypeName]
    FROM    [Sys_ActivityMaster] LEFT OUTER JOIN [Sys_ActivityTypeMaster] 
	ON      [Sys_ActivityMaster].[ActivityTypeKey] = [Sys_ActivityTypeMaster].[ActivityTypeKey]
    WHERE   (
	          [Sys_ActivityMaster].[ActivityKey] NOT IN  
			(
	SELECT    [Sys_ActivityMaster].[ActivityKey]
    FROM      [Sys_ActivityMaster] RIGHT OUTER JOIN [Sys_RoleActivity] 
	ON        [Sys_ActivityMaster].[ActivityKey] = [Sys_RoleActivity].[RoleActivityKey]
    WHERE   (
	           [Sys_RoleActivity].[RoleKey] = @RoleKey
			)
		    )
		    )
END
GO
